import pandas as pd
import re
from xpinyin import Pinyin


# %%
def load_data(path):
    return pd.read_excel(path, header=None)


# %%
def change_str_style(str):  # 将字符串格式转换为首字母大写其他小写的形式
    return re.sub(r'[\W]', '', str).capitalize()


# %%
def col_4_process(string):
    table = {ord(f): ord(t) for f, t in zip(
        u'，。！？【】（）％＃＠＆１２３４５６７８９０',
        u',.!?[]()%#@&1234567890')}
    try:
        string = string.translate(table)  # 将标点转位英文标点
    except:
        string = string
    string = Pinyin().get_pinyin(f'{string}', ' ')  # 转拼音 耗时长
    return string


# %%
from datetime import datetime


# 改变时间格式：
def change_time_type(time):
    time = time.replace(":", "")
    return time[:2] + ':' + time[2:4] + ':' + time[4:6]


input_string = "0:00:03.4"
result = change_time_type(input_string)
print(result)  # 输出：000003


def time_difference(str_time1, str_time2):
    # 将字符串时间转换为datetime对象
    time_format = "%H:%M:%S"
    time1 = datetime.strptime(change_time_type(str_time1), time_format)
    time2 = datetime.strptime(change_time_type(str_time2), time_format)

    # 计算时间差值
    time_diff = time2 - time1

    # 获取总秒数和毫秒数
    total_seconds = time_diff.total_seconds()
    milliseconds = int(total_seconds * 1000)

    # 转换为"0.几秒"的格式
    seconds = milliseconds / 1000.0

    return seconds


'''
# 例子
time1_str = "00:00:03.3"
time2_str = "00:00:04.9"

result = time_difference(time1_str[:8], time2_str[:8])
print("时间差值:", result, "秒")
'''


# %%
def sum_time(str):
    time_list = re.split(r':', str)
    h = float(time_list[0])
    m = float(time_list[1])
    s = float(time_list[2])
    m += s / 60
    h += m / 60
    h = format(h, '.2f')
    return h


# %%
# 更新teacher 删除了old code 暂时没有 

# %%
path = '46.xlsx'  # path = input()# 输入文件名称

dataset = load_data(path)

teacher_data = pd.read_csv('teacher.csv', header=0)

teacher_name = [change_str_style(each) for each in teacher_data['Teacher real name'].tolist()]

teacher_code = [change_str_style(each) for each in teacher_data['New code'].tolist()]

check_list = teacher_data['New code'].tolist() + teacher_data[
    'Teacher real name'].tolist()  # 汇聚了所有老师的姓名和newcode未经过大小写转换 查第一行的第三列是否在这当中

student_name_dataset = pd.read_csv('save_id_student.csv', header=0)  # 用于读取最后的chat_id 和student_id

student_id = student_name_dataset.iloc[:, :1].values.tolist()[0][0]

col_id = student_name_dataset.iloc[:, 1:2].values.tolist()[0][0]

student_dataset = pd.read_excel('all_stu_code.xlsx', header=None)

teacher_name_code_dict = dict(zip(teacher_name, teacher_code))  # 创建一个老师名字和new_code对应的dict

code_time = pd.read_csv('code_time.csv')

# 将code_time 表中的第一二列作为一个dict
time_dict = dict(
    zip([i[0] for i in code_time.iloc[:, :1].values.tolist()], [i[0] for i in code_time.iloc[:, 1:2].values.tolist()]))

# %%
# 提取导入数据的所有列，默认有4列（故只提取前4列
co1_1 = [i[0] for i in dataset.iloc[:, :1].values.tolist()]  # startime
col_2 = [i[0] for i in dataset.iloc[:, 1:2].values.tolist()]  # endtime
col_3 = [change_str_style(i[0]) for i in
         dataset.iloc[:, 2:3].values.tolist()]  # teacher_name or new_code 如果不是teacher name 则将其视为student
col_4 = [i[0] for i in dataset.iloc[:, 3:4].values.tolist()]  # conversation
col_5 = [col_3[0] for i in range(len(col_3))]  # 增加一个teacher列

if change_str_style(col_3[0]) not in check_list:  # 判断第3行第一列也就是导入文件的teacher name or code是否存在于原有的teacher文件中 不存在则抛出错误
    raise RuntimeError('Code or Name not in all teachers-code list please update the teacher table')

# 判断时间格式是否正确 若不符合"%H:%M:%S" 则报错
datetime.strptime(change_time_type(col_2[-1]), "%H:%M:%S")

for i in range(len(col_3)):  # 对第三行进行处理 处理方式为如果是对应teacher name则将其转换为 code 若无对应teachername 则将其对应转换为stu_X
    try:
        if col_3[i] in teacher_code:  # 若已经是teacher code 则无需修改
            col_3[i] = col_3[i]
        else:
            col_3[i] = teacher_name_code_dict[col_3[i]]
    except:
        col_3[i] = 'Stu_' + f'{student_id}'
        student_id += 1

for i in range(len(col_4)):  # 对第4行进行处理
    col_4[i] = col_4_process(col_4[i])

# 加一个停顿时间列
col_6 = [time_difference('00:00:00', co1_1[0])]

# 错误检测，如果有误则把上结束变成下开始，或者把下开始变成上结束
for i in range(1, len(col_2)):
    try:
        col_6.append(time_difference(col_2[i - 1][:8], co1_1[i][:8]))
    except:
        try:
            time_difference('00:00:00', co1_1[i][:8])  # 判断是不是上开始有误

            col_2[i - 1] = co1_1[i][:8]
            col_6.append(time_difference(col_2[i - 1][:8], co1_1[i][:8]))
        except:
            co1_1[i] = col_2[i - 1][:8]
            col_6.append(time_difference(col_2[i - 1][:8], co1_1[i][:8]))

col_id += 1
col_id_list = [col_id for i in range(len(co1_1))]
chat_id = [i + 1 for i in range(len(co1_1))]

# 更新模块对应时间
time_dict[col_3[0]] += float(sum_time(col_2[-1]))

# 大表save
save_data = pd.DataFrame({'col_1': col_id_list, 'col_2': chat_id, 'col_3': col_4, 'col_4': col_2
                             , 'col_5': co1_1, 'col_6': col_3, 'col_7': col_5
                             , 'col_8': col_6})

save_data.to_csv('./conversation.csv', mode='a', index=False, header=False)
# student 表 save
save_data = pd.DataFrame({'col_1': [student_id], 'col_2': [col_id]})
save_data.to_csv(f'./save_id_student.csv', index=False, header=None)
# time 表save
save_data = pd.DataFrame({'col_1': time_dict.keys(), 'col_2': time_dict.values()})
save_data.to_csv(f'./code_time.csv', index=False, header=None)
